package edu.cmu.cs214.hw9.db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import edu.cmu.cs214.hw9.facelook.Post;
import edu.cmu.cs214.hw9.json.JSONArray;
import edu.cmu.cs214.hw9.json.JSONException;
import edu.cmu.cs214.hw9.json.JSONObject;

import edu.cmu.cs214.hw9.db.*;  // ADDED SO WE CAN USE WHAT IS IN SHARD UTILS - SARAH G. SPOCK
/**
 * A database handler that interacts with the database via SQL statements, via PreparedStatement
 * and ResultSets.  
 * @author davidzheng
 *
 */


public class UserData extends SQLiteAdapter {
	//Stuff that connects to the database
		private int port;   // LOCATION OF THE SHARD OF USER DATA
		// I EDITED THIS CONSTRUCTOR TO BE SHARD FRIENDLY. PLEASE SEE SQLiteAdapter.java ! - Sarah
		public UserData(String databaseName, int portToAssignTo) throws Exception {
			super(databaseName);
			this.port = portToAssignTo;
		}
		
		// ADDED FOR SHARDING: SARAH SPOCK
		public int getPort() {
			return port;
		}
	/**
	 * Gets an arraylist of the given user's friends.  If the pending flag is true, 
	 * the method will return a list all people who have friend requested the given
	 * email.  If pending if false, the method returns a list of emails that are actually friends
	 * with the user.
	 * @param email the email of the person in question
	 * @param pending whether or not to return a list of pending friends
	 * @return A list of all friends
	 */
	public ArrayList<String> getFriends(String email, boolean pending){
		//The method uses pending to determine what sql statement to use, executes the
		//statement to get the JSON string representation of the list of friends, then
		//converts the string into an arraylist by first converting the string to a JSONArray,
		//then converts string to array.
		
		//This will be the result of our query
		ResultSet rs = null;
		//This will be the string extracted from the database, since the friends data
		//is stored as a jsonString
		String jsonString = null;
		
		//if we want pending friends, we choose from pending column.  if not, we don't
		String col;
		if (pending) col = "pending";
		else col = "friends";
		
		//the list to eventually return
		ArrayList<String> ret = new ArrayList<String>();
		
		try {
			//The query
			//String statement = "SELECT ? FROM users WHERE email=?";
			//didn't work
			
			String statement = "SELECT " + col + " FROM users WHERE email=?";
			//compiles the query
			PreparedStatement ps = conn.prepareStatement(statement);
			ps.setString(1, email);//sets ? to email
			rs = ps.executeQuery();//executes the query, returns the result
			//iterates through the result, add all emails to 
			while(rs.next()){//should only be 1 item, since email is unique
				jsonString = rs.getString(col);//the json Text representing a JSONArray
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
            try{
            	if(rs != null){
            		rs.close();//closing connection when done
            	}
            } catch (SQLException e){
            }
        }
		//if user has no friends, return empty list
		if (jsonString == null)
			return ret;
		
		//create JSONArray from the jsonString, then iterate and add all friend emails to arraylist
		try {
			JSONArray jsonFriends = new JSONArray(jsonString);
			for(int i = 0;i<jsonFriends.length(); i++)
				ret.add(jsonFriends.getString(i));
			
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return ret;
	}
	/**
	 * Processes a friend request between requester and requestee
	 * @param the user who is requesting to be friends
	 * @param the user who is being requested to be friends 
	 * @return true if the friend request succeeds
	 */
	public boolean requestFriend(String rqer, String rqee)
	{
		/*
		 * Processing a friend request consists of adding the requester to the
		 * requestee's list of pending friend requests.  Will return false if
		 * requester has already requested to be friends
		 */
		
		//checks to see if requester is on requestee's list of friend requests,
		//returns false if so
		ArrayList<String> pending = getFriends(rqee,true);
		if (pending.contains(rqer)) return false;

		pending.add(rqer);//adds requester to list
		String jsonString = new JSONArray(pending).toString();//converts list to storable string
		
		PreparedStatement ps;
		String statement = "UPDATE " + "users" + " SET pending=? WHERE email=?";
		try{
			ps = conn.prepareStatement(statement);
			ps.setString(1, jsonString);// first ? = jsonString
			ps.setString(2, rqee);//second ? = requestee email
			ps.executeUpdate();//executes the update
		} catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		return true;
		
	}

	/**
	 * Process an accepted friend request 
	 * @param rqer The one who requested to be friends
	 * @param rqee The one who accepted
	 * @return true if successful
	 */
	public boolean acceptFriend(String rqer, String rqee)
	{
		//when accepting A's friend request to B, we make sure 1.) A is no longer on B's 
		//list of pending friend requests, 2.) A is in B's list of friends, and 3.) B is in
		//A's list of friends.
		
		ArrayList<String> pending = getFriends(rqee,true);
		ArrayList<String> rqeeFriends = getFriends(rqee,false);
		ArrayList<String> rqerFriends = getFriends(rqer,false);
		
		//if any of the 3 conditions above are already true, do nothing, return false
		if (!pending.contains(rqer)||rqeeFriends.contains(rqer)||rqerFriends.contains(rqee)) return false;
		pending.remove(rqer); //step 1.)
		rqeeFriends.add(rqer); //step 2.)
		rqerFriends.add(rqee); //step 3.)
		String jsonPend = new JSONArray(pending).toString();
		String jsonFrndee = new JSONArray(rqeeFriends).toString();
		String jsonFrnder = new JSONArray(rqerFriends).toString();
		
		PreparedStatement ps;
		String statement1 = "UPDATE " + "users" + " SET pending=?, friends=? WHERE email=?";
		String statement2 = "UPDATE " + "users" + " SET friends=? WHERE email=?";
		try{
			ps = conn.prepareStatement(statement1);
			ps.setString(1, jsonPend);// first ? = rqee's list of friend requests
			ps.setString(2, jsonFrndee);//second ? = rqee's list of friends
			ps.setString(3, rqee);//rqee's email
			ps.executeUpdate();
			
			ps = conn.prepareStatement(statement2);
			ps.setString(1, jsonFrnder);//rqer's list of friends
			ps.setString(2, rqer);//rqer's email
			ps.executeUpdate();
		} catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		if (isSubscribedTo(rqer, rqee)) unSubscribe(rqer, rqee);
		if (isSubscribedTo(rqee, rqer)) unSubscribe(rqee, rqer);
		return true;
		
	}
	
	/**
	 * Unfriends the hater who doens't want to be friends anymore, and the victim
	 * who won't be friends with the hater anymore
	 * @param hater the unfriender
	 * @param victim the unfriendee
	 * @return true on success
	 */
	public boolean removeFriend(String hater, String victim)
	{
		/*
		 * To remove friend, we have to remove the two users from each other's
		 * respective friends lists, so we will get the list of friends,
		 * update them to reflect changes, then update teh database with these
		 * new lists
		 */
		//if they aren't friends, do nothing
		if (!areFriends(hater, victim, false)) return false;
		
		
		ArrayList<String> haterFriends = getFriends(hater,false);
		ArrayList<String> victimFriends = getFriends(victim,false);
		
		haterFriends.remove(victim);
		victimFriends.remove(hater);
		String jsonHater = new JSONArray(haterFriends).toString();
		String jsonVictim = new JSONArray(victimFriends).toString();
		
		PreparedStatement ps;
		String statement = "UPDATE " + "users" + " SET friends=? WHERE email=?";
		try{
			ps = conn.prepareStatement(statement);
			ps.setString(1, jsonHater);// first ? = hater's list of friends
			ps.setString(2, hater);//second ? = hater's email
			ps.executeUpdate();
			
			ps = conn.prepareStatement(statement);
			ps.setString(1, jsonVictim);//victim's list of friends
			ps.setString(2, victim);//victim's email
			ps.executeUpdate();
		} catch(SQLException e){
			return false;
		}
		
		return true;
	}
	/**
	 * Checks to see if the two users are friends, OR if the first user has
	 * friend requested the second user, depending on the pending variable.  If
	 * pending is true, it does the latter; if false, it does the former
	 * @param friender first user
	 * @param friendee second user
	 * @param pending true if we want to see if friender has requested friendee's
	 * freindship, false if we want to see if the two are actually friends
	 * @return the result of the check
	 */
	public boolean areFriends(String friender, String friendee, boolean pending)
	{
		if (friender.equals(friendee)) return false; //not friends with oneself
		if (!userExists(friender) || !userExists(friendee)) return false;  //if you don't exist, you ain't friends
		
		//Sees if friendee is in list of friender's friends.
		ArrayList<String> friends = getFriends(friendee, pending);
		if (friends.contains(friender)) return true;
		else return false;
		
	}
	/**
	 * Gets a list of user's that the current user has subscribed to
	 * @param email the email of the user
	 * @return a List of subscriptions of the user
	 */
	public ArrayList<String> getSubscriptions(String email){
		/*
		 * To do this, we merely get the list that is stored in the database of
		 * subscriptions, and then put it in an arrayList.
		 */
		ResultSet rs = null;
		String jsonString = null;
		
		//the list to eventually return
		ArrayList<String> ret = new ArrayList<String>();
		try {
			String statement = "SELECT subs FROM users WHERE email=?";
			PreparedStatement ps = conn.prepareStatement(statement);
			ps.setString(1, email);//email address of user in question
			rs = ps.executeQuery();//actually executes the query
			while(rs.next()){//should only be 1 item, since email is unique
				jsonString = rs.getString("subs");//the json Text representing a JSONArray
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
            try{
            	if(rs != null){
            		rs.close();//closing connections is healthy
            	}
            } catch (SQLException e){
            }
        }
		if (jsonString == null) //if user has no subscriptions
			return ret; //return an empty list
		
		try {
			JSONArray jsonSubs = new JSONArray(jsonString);
			for(int i = 0;i<jsonSubs.length(); i++)
				ret.add(jsonSubs.getString(i));//add subscriptions one by one to arrayList
			
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return ret;//return the arraylist
	}
	/**
	 * Checks to see if the first user is subscribed to the second user
	 * @param reader first user
	 * @param author second user
	 * @return true if the check is true
	 */
	public boolean isSubscribedTo(String reader, String author)
	{
		if (reader.equals(author)) return false; //not subscribed to self
		if (!userExists(reader) || !userExists(author)) return false;  //not existing->not subscribing
		
		//checks to see if author is in reader's subscriptions list
		ArrayList<String> subs = getSubscriptions(reader);
		if (subs.contains(author)) return true;
		else return false;
	}
	/**
	 * Subscribes the first user to the second user's notifications
	 * @param reader first user
	 * @param author second user
	 * @return true on success
	 */
	public boolean subscribeTo(String reader, String author)
	{
		/*
		 * To do this, we must get the reader's list of subscriptions, add the
		 * author, and then store the new list in the database for the reader's
		 * subscriptions
		 */
		
		//If already friends, subscribing is redundant and wrong
		if (areFriends(reader, author, false)) return false;
		
		ArrayList<String> readerSubs = getSubscriptions(reader); //list of subscriptions
		
		//if already subscribed, don't do it again
		if (readerSubs.contains(author)) return false;
		
		readerSubs.add(author);
		
		String jsonSubs = new JSONArray(readerSubs).toString();
		
		PreparedStatement ps;
		String statement1 = "UPDATE " + "users" + " SET subs=? WHERE email=?";
		try{
			ps = conn.prepareStatement(statement1);
			ps.setString(1, jsonSubs);
			ps.setString(2, reader);
			ps.executeUpdate();
			
		} catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		
		return true;
	}
	/**
	 * Unsubscribes the first user from the second
	 * @param reader the first user
	 * @param author the second user
	 * @return true on success
	 */
	public boolean unSubscribe(String reader, String author)
	{
		/*
		 * To unsubscribe the reader from the author, we get the reader's subscriptions,
		 * remove the author, then re-store the new list in the database
		 */
		//If not subscriber, do nothing
		if (!isSubscribedTo(reader, author)) return false;
		
		ArrayList<String> readerSubs = getSubscriptions(reader);
		
		readerSubs.remove(author);
		
		String jsonSubs = new JSONArray(readerSubs).toString();
		
		PreparedStatement ps;
		String statement1 = "UPDATE " + "users" + " SET subs=? WHERE email=?";
		try{
			ps = conn.prepareStatement(statement1);
			ps.setString(1, jsonSubs);
			ps.setString(2, reader);
			ps.executeUpdate();
			
		} catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		
		return true;
	}
	/**
	 * Checks to see if a user exists
	 * @param email the user's email
	 * @return true if exists
	 */
	public boolean userExists(String email)
	{
		/*
		 * To do this, we will use an SELECT query to get all rows containing 
		 * the given username.  If the user exists, there should only be 1 row,
		 * since e-mail is unique, and we will return true.  If the user doesn't
		 * exist, there will be no rows, and we will return false.
		 */
		ResultSet rs = null;
		boolean exists = false;
		try{
			PreparedStatement ps;
			String statement = "SELECT * from users WHERE email=?;";
			ps = conn.prepareStatement(statement);
			ps.setString(1, email);
			rs = ps.executeQuery();
			if (rs.next()) //if a row exists
				exists = true;
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return false; //arbitrary, not sure what to return in this case
		}
		finally {
            try{
            	if(rs != null){
            		rs.close();
            	}
            } catch (SQLException e){
            }
		}
		return exists;
	}
	/**
	 * Get's the password for the given email
	 * @param email the email
	 * @return the password
	 */
	public String getPassword(String email)
	{
		/*
		 * To do this, we simply use a SELECT query to get the row for the user,
		 * then get the value in the password column.
		 */
		String pass = null;
		ResultSet rs = null;
		try{
			PreparedStatement ps;
			String statement = "SELECT password from users WHERE email=?;";
			ps = conn.prepareStatement(statement);
			ps.setString(1, email);
			rs = ps.executeQuery();
			while (rs.next()){ //should only be one element, since email is unique
				pass = rs.getString("password");
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return null; //arbitrary, not sure what to return in this case
		}
		finally {
            try{
            	if(rs != null){
            		rs.close();
            	}
            } catch (SQLException e){
            }
		}
		return pass;
	}
	/**
	 * Get's the name associated with the given email
	 * @param email the email
	 * @return the Name
	 */
	public String getName(String email)
	{
		/*
		 * To do this, we simply use a SELECT query to get the row for the user,
		 * then get the value in the username column.
		 */
		String name = null;
		ResultSet rs = null;
		try{
			PreparedStatement ps;
			String statement = "SELECT username from users WHERE email=?;";
			ps = conn.prepareStatement(statement);
			ps.setString(1, email);
			rs = ps.executeQuery();
			while (rs.next()){ //should only be one element, since email is unique
				name = rs.getString("username");
			}
		}
		catch (SQLException e)
		{
			e.printStackTrace();
			return null; //arbitrary, not sure what to return in this case
		}
		finally {
            try{
            	if(rs != null){
            		rs.close();
            	}
            } catch (SQLException e){
            }
		}
		return name;
	}
	/* Important Note: We are Sharding Users Into Different Databases Lexographically according to email. The Number of Shards is in the Constants File, the desired shard is determined
     * based upon hashing the string of the email and then modding it by the number of shards.  - Sarah G. Spock
     *
     * This doesnt need to be edited, because it should only be feeded information for its specific letter. See the Server.java for the hash table of the shard info.  Shard info:  
     */
	/**
	 * Adds a user to the database
	 * @param username the name
	 * @param email the email
	 * @param password the password
	 * @return true on success
	 */
	public boolean addUser(String username, String email, String password){
		//use an INSERT sql statement
		
		PreparedStatement ps;
		
		String statement = "INSERT INTO " + "users" + " (username, email, password) VALUES (?, ?, ?)";
		try{
			ps = conn.prepareStatement(statement);
			ps.setString(1, username);
			ps.setString(2, email);
			ps.setString(3, password);
			ps.executeUpdate();
		} catch(SQLException e){
			e.printStackTrace();
			System.out.println(e.getMessage());
			return false;
		}
		
		return true;
		
	}
	/**
	 * Gets all the status's or notifications of the user, depending on the
	 * value of the status variable.  If status is true, get statuses; if false,
	 * get notifications
	 * @param email the email
	 * @param status true if we want statuses, false if we want notifications
	 * @return A list of the Posts
	 */
	public LinkedList<String> getPosts(String email, boolean status)
	{
		/*
		 * Since we store lists to represent posts, this is simply a matter
		 * of retrieving said lists from the database, and parsing/converting
		 * to the correct format
		 */
		ResultSet rs = null;
		String jsonString = null;
	
		String col;
		if (status) col = "status";
		else col = "notifs";
		LinkedList<String> ret = new LinkedList<String>();
		try {
			String statement = "SELECT " + col + " FROM users WHERE email=?";
			PreparedStatement ps = conn.prepareStatement(statement);
			ps.setString(1, email);//email address of user in question
			rs = ps.executeQuery();//actually executes the query
			while(rs.next()){//should only be 1 item, since email is unique
				jsonString = rs.getString(col);//the json Text representing a JSONArray
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
            try{
            	if(rs != null){
            		rs.close();//closing connections is healthy
            	}
            } catch (SQLException e){
            }
        }
		if (jsonString == null) //if user has no news
			return ret; //return an empty list
		
		try {
			JSONArray jsonPosts = new JSONArray(jsonString);
			for(int i = 0;i<jsonPosts.length(); i++)
				ret.add(jsonPosts.getString(i));//add posts one by one to arrayList
			
		} catch (JSONException e) {
			e.printStackTrace();
		}
		return ret;
	}
	/**
	 * Processes notification or status post 
	 * @param poster the poster
	 * @param postData A string representing the data to be stored
	 * @param status true if we are posting status, false if notification
	 * @return true on success
	 */
	public boolean postMessage(String poster, String postData, boolean status)
	{
		/*
		 * To do this, we get a the list of the user's statuses or notifications,
		 * add the new post, delete the oldest one if there are more than 10,
		 * then update the database with this new list.
		 */
		LinkedList<String> posts = getPosts(poster, status);
		posts.addFirst(postData);
		
		//We will only need to keep track of 10 for this assignment
		if (posts.size()>10)
			posts.removeLast();
		
		String jsonPosts = new JSONArray(posts).toString();
		
		String col;
		if (status) col = "status";
		else col = "notifs";
		PreparedStatement ps;
		String statement1 = "UPDATE " + "users" + " SET " + col + 
				"=? WHERE email=?";
		try{
			ps = conn.prepareStatement(statement1);
			ps.setString(1, jsonPosts);
			ps.setString(2, poster);
			ps.executeUpdate();
			
		} catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		
		return true;
		
	}
	
	
	//debugging stuff
	public boolean clearDB()
	{
		String statement = "DELETE from users";
		try{
			PreparedStatement ps = conn.prepareStatement(statement);
			ps.executeUpdate();
		} catch(SQLException e){
			System.out.println(e.getMessage());
			return false;
		}
		return true;
	}
	public void printDB()
	{
		ResultSet rs = null;
		try {
			String statement = "SELECT username, email, password FROM users";
			PreparedStatement ps = conn.prepareStatement(statement);
			
			rs = ps.executeQuery();
			while(rs.next()){//go through the ResultSet
				System.out.println(rs.getString("username")+ "\t" + rs.getString("email") + 
						"\t" + rs.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
            try{
            	if(rs != null){
            		rs.close();
            	}
            } catch (SQLException e){
            }
        }
	}
	
	
}
